1. Field of the Invention
The present invention applies to the field of On-Line Analytical Processing (OLAP); and specifically, to the field of On-Line Analytical Processing that accesses relational databases.
2. Discussion of the Related Art
A significant advantage of an OLAP data source is that the data are organized in an understandable manner for the end user. The physical representation of the data is separated from the semantic layer presented to the end user. It can be very useful to help the end user to define this kind on layer on top of any type of data source. An ideal tool takes any heterogonous data source and displays the data in a unified semantic layer.
You can see each hierarchy that composes a dimension one different dimension by hierarchy.
According to the present invention, it is possible to map all relational models to OLAP models. The process is highly automated according to the present invention.
An OLAP data source is a database like a RDBMS. The OLAP model is similar to a data warehouse. It is composed of one or more fact table (multi-cube versus hypercube) and dimensions table links this fact table. In the OLAP model, you do not see the key and relation, only the useful object. The object is typed (dimension, measure, member) and presented in an organized manner. An OLAP data source use important abstraction between the data storage and the data representation.
Two differences between an OLAP data source and a RDBMS are evident: namely the speed and the capacity to perform complex calculations.
There is need to leverage the gap between the data sources capabilities to offer a generic query tool that is not dependent of the data source. A difference between the two models is the complex calculations such as row based calculations in the OLAP model. The row base calculations are possible, but very complicated with a RDBMS and the SQL language.
A foreign key is an attribute (or attribute combination) in one relation R2 whose values are required to match those of the primary key of some relation R1 (R1 and R2 not necessarily distinct).
The community of OLAP vendors has established a set of de-facto standards to model multi-dimensional data representations. The most famous of these is the Microsoft""s OLEDB for OLAP standard (ODBO), which is being endorsed by a vast majority of multidimensional databases and analytical tools vendors. This standard is well adapted to classical and somewhat limited analytical needs, but Analytical Reporting technology according to the present invention creates an opportunity to extend it in a powerful way.
A multidimensional data model according to the present invention has been established that is fully compatible with ODBO in terms of concepts, but extends it significantly.
The present invention includes several unique aspects.
All OLAP tools rely upon a preconceived storage of data to analyze. The data is usually stored either in a simple relational database, in which case the tool is called a Relational On Line Analytical Processing tool, or into a multidimensional storage, in which case the tool is deemed a MOLAP tool. Hybrid tools, that support both forms of storage, also exist.
ROLAP tools impose strong constraints on the underlying relational scheme. This scheme is necessarily either a snowflake or a star scheme, and additional constraints are usually set on how the fact tables or aggregate tables are built.
According to the present invention, relational data can be mapped onto an Extended Multidimensional Data Model regardless of the database scheme. From a Business Objects Universe built on top of the database, and possibly by adding some metadata, a multidimensional representation is derived for any relational database scheme.
According to the present invention, a Relational Database Management System (RDBMS) having any arbitrary structure is translated into a multi-dimensional data model suitable for performing OLAP operations upon. If a relational table defining the relational model includes any tables with cardinality of 1,1 or 0,1, the tables are merged into a single table according to the present invention. If the relational table is not normalized, then normalization is performed and a relationship between the original table and the normalized table is created. If the relational table is normalized, but not by dependence between columns, such as in the dimension table in a snowflake schema, the normalization process is performed using the foreign key in order to generate the normalized table according to the present invention.
Once the normalized table is generated, OLAP measures are derived from the normalized relational table by an automated method according to the present invention. In addition, OLAP dimensions are derived from the normalized relational table and the results of the OLAP measures derivation by an automated method according to the present invention.
Conventional OLAP tools do not offer such capabilities.
According to the present invention, it is possible to associate a member of a dimension to another member of the same or another dimension. This may be used freely when doing reporting or analysis. Thus according to the present invention, functional dependencies can be modeled. Functional dependencies cannot be captured by a pure multi-dimensional model. This aspect of the present invention makes it easy, for instance, to create a report that displays, for each country, the revenue generated worldwide by the sales persons who are located in this country. Conventional OLAP technologies are unable to allow the computing of this in a single step.
According to another aspect of the present invention, it is possible to create a new dimension of analysis, the members of which are all the different values that a scalar expression can take on. This aspect of the present invention allows for easy building of a matrix that yields the revenue per quarter and per year for instance. Conventional OLAP technologies are not capable of doing this in an automated manner.
According to yet another aspect of the present invention, it is possible to access the various instances of a Reporting Object as members in an OLAP dimension. This aspect of the present invention allows for the manipulation of one specific cell in one specific table, similarly to the way a spreadsheet operates. Conventional OLAP technologies do not provide for such fine-grained computations.
Some relational or multidimensional Databases possess so-called xe2x80x9copaque filtersxe2x80x9d, therefore objects that will filter data in a way that cannot be exposed in a multidimensional model. An example of such filters is a complex xe2x80x9cwherexe2x80x9d clause in an SQL statement.
According to the yet another aspect of the present invention, it is possible to apply these filters or a combination of them to the data that underlies analysis. Conventional OLAP technologies do not allow for integration of non-multidimensional filters.